package com.ey12.file;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.ey12.utils.DateUtil;

/** 
 * 
 * @author crystal
 */
public class FileServerArea extends HttpServlet {
	private static final String CONTENT_TYPE = "text/xml; charset=utf-8";
	private static String URL = "";
	private static String USN = "";
	private static String PWD = "";

	/**
	 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
	 * methods.
	 * 
	 * @param request
	 *            servlet request  
	 * @param response
	 *            servlet response
	 */
	// // �����ļ����ϴ�·��
	// private String uploadPath = "d:\\a\\";
	// �����ļ����ϴ���С
	private int maxPostSize = 100 * 1024 * 1024;

	public FileServerArea() {
		super();
	}

	public void destroy() {
		super.destroy();
	}

	protected void processRequest(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		URL = this.getServletContext().getInitParameter("DbUrl");
		USN = this.getServletContext().getInitParameter("DbUsername");
		PWD = this.getServletContext().getInitParameter("DbPwd");
		
		request.setCharacterEncoding("UTF-8");
		
		System.out.println(request.getServletPath());
		System.out.println(request.getSession().getServletContext()
				.getRealPath(request.getRequestURI()));
		System.out.println("********************FileServerArea*********************");
		response.setContentType("text/html;charset=UTF-8");
		PrintWriter out = response.getWriter();
		
		String uploadDirectory = this.getServletContext().getInitParameter(
				"UploadDirectory");
		// �����ļ�����������
		ArrayList<String> allowedFormats = new ArrayList<String>();
		allowedFormats.add("csv");
		allowedFormats.add("xls");
		allowedFormats.add("xlsx");

		DiskFileItemFactory factory = new DiskFileItemFactory();
		factory.setSizeThreshold(8192);
	
		
		ServletFileUpload upload = new ServletFileUpload(factory);
//		upload.setHeaderEncoding("utf-8");
		upload.setSizeMax(maxPostSize);
		try {
			List fileItems = upload.parseRequest(request);
			Iterator iter = fileItems.iterator();
			while (iter.hasNext()) {
				FileItem item = (FileItem) iter.next();
				if (!item.isFormField()) {
					String name = item.getName();						
					System.out.println("-"+uploadDirectory + name);						
					String[] names = name.split("\\.");					
					if (allowedFormats.contains(names[1])) {
						try {
							System.out.println(name+"-"+names[0]+"-"+names[1]+"-"+uploadDirectory + name);							
							File file = new File(uploadDirectory + name);
							item.write(file);
							readExcel(file);
						} catch (Exception e) {
							e.printStackTrace();
						}
					} 
				}
			}
		} catch (FileUploadException e) {
			e.printStackTrace();
			System.out.println(e.getMessage() + "����");
		}
	}

	/**
	 * Handles the HTTP <code>GET</code> method.
	 * 
	 * @param request
	 *            servlet request
	 * @param response
	 *            servlet response
	 */
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		processRequest(request, response);
	}
  
	/**
	 * Handles the HTTP <code>POST</code> method.
	 * 
	 * @param request
	 *            servlet request
	 * @param response
	 *            servlet response
	 */
	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		processRequest(request, response);
	}

	/**
	 * Returns a short description of the servlet.
	 */
	public String getServletInfo() {
		return "Short description";
	}

	public void readExcel(File file) {
		try {
			// ��Ϸ�� ��Ϸ���� ��Ϸ������ ��Ӫ �������
			// ���³�����ʿ ������ ��1�� j�� ���
//			<!-- 
//			������IP 218.64.131.24
//			root
//			admin5123
//			MYSQL����:s2ls3kxe
//			 -->
			HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));
			HSSFSheet sheet = workbook.getSheetAt(0);
			if(sheet==null){
				System.out.println("********************sheet is null*********************");				
				return;
			}
			List list = new ArrayList();
			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				HSSFRow row = sheet.getRow(i);
				HSSFCell game = row.getCell((short) 0);
				String gameValue = null;
				if(game!=null){
					gameValue = game.getStringCellValue();
					if(gameValue!=null){
						if(!"".equals(gameValue.trim())){
							gameValue = game.getStringCellValue();
						}else{
							break;
						}
					}
				}else{
					break;
				}
				HSSFCell cserver = row.getCell((short) 1);
				String cserverValue = cserver.getStringCellValue();
				HSSFCell servertype = row.getCell((short) 2);
				String servertypeValue = servertype.getStringCellValue();
				HSSFCell camp = row.getCell((short) 3);
				String campValue = camp.getStringCellValue();
				HSSFCell moneytype = row.getCell((short) 4);
				String moneytypeValue = moneytype.getStringCellValue();	
				HSSFCell danweitype = row.getCell((short) 5);
				String danweiValue = danweitype.getStringCellValue();	
				
				
				System.out.println("********************gameValue*********************"+gameValue);
				int gameid = findGameId(gameValue.trim());
				System.out.println("********************gameid*********************"+gameid);
				if (gameid != -1) {
					int cserverid = findCserverId(gameid, cserverValue,
							servertypeValue, moneytypeValue, campValue,danweiValue);
					System.out.println("********************cserverid*********************"+cserverid);
					if (cserverid == -1) {
						String sql = "insert into gm_cserver(game_id,cserver_name,sever_type,camp,unit,danwei) ";
						sql = sql + " values(" + gameid + ",'" + cserverValue
								+ "','" + servertypeValue + "','" + campValue
								+ "','" + moneytypeValue + "','"+danweiValue+"')";
						list.add(sql);
					}
				} 
			}
			saveGmStore(list);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("������xlRead() : " + e);
		}

	}

	public String reverse(String item) {
		if ("��".equals(item)) {
			return "2";
		} else {
			return "1";
		}
	}

	public static Connection getConnectionByJDBC() {
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		try {
			conn = DriverManager.getConnection(URL, USN, PWD);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	public int findCserverId(int gameId, String cserverName, String severType,
			String unit, String camp,String danwei) {
		int id = -1;
		String sqlx = "select g.id from gm_cserver g where g.game_id=" + gameId
				+ " and g.cserver_name='" + cserverName
				+ "' and g.sever_type='" + severType + "' and g.unit='" + unit
				+ "' and g.camp='" + camp + "' and g.danwei='"+danwei+"'";
		System.out.println("findCserverId="+sqlx);
		try {

			Connection conn=getConnectionByJDBC();
			
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlx);
			if (rs.next()) {
				id = rs.getInt("id");
				rs.close();
				stmt.close();
				conn.close();
				return id;
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		} 
		return id;
	}

	public int findGameId(String gameName) {
		int id = -1;
	
		String sqlx = "select id from gm_game where game_name='" + gameName
				+ "'";
		System.out.println("findGameId="+sqlx);
		try {
			Connection conn=getConnectionByJDBC();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlx);
			if (rs.next()) {
				id = rs.getInt("id");
				rs.close();
				stmt.close();
				conn.close();
				return id;
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		} 
		return id;
	}

	public void save(String sql) {
		Connection conn=getConnectionByJDBC();
		try {
			Statement stmt = conn.createStatement();
			stmt.execute(sql);
			stmt.close();
			conn.close();

		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		}
	}

	public void saveGmStore(List list) {
		Connection conn=getConnectionByJDBC();
		try {
			Statement stmt = conn.createStatement();
			
			for (int i = 0; i < list.size(); i++) {
				System.out.println(">>>>>>>>>>>>>>"+list.get(i).toString());
				stmt.execute(list.get(i).toString());
			}
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	

}
